<?php
namespace app\index\controller;
use think\Controller;
use think\Request;
use think\captcha\Captcha;
use think\facade\Session;
use think\facade\Config;
use think\Db;

class Brand extends Base
{
	public function index(Request $request){
		$data['base_data']=$this->base_data;
		$class_id = request()->param()['class_id'];
        $class_seo=Db::table('lily_classify_seo')->where([['status','=',1],['class_id','=',$class_id]])->find();
        $data['seo_title']=$class_seo['title'];
        $data['seo_keywords']=$class_seo['keywords'];
        $data['seo_desc']=$class_seo['description'];
        $data['crumbs']=get_crumbs($class_id);/*面包屑*/ 
        $data['class_info']=Db::table('lily_classify')->where([['status','=',1],['id','=',$class_id]])->find();/*分类本身数据*/
        $data['lily_brand']=Db::table('lily_brand')->where('status','=',1)->order('seq')->select();
        $data['classify_content']=Db::table('lily_classify_content')->where('class_id','=',$class_id)->find();/*分类的内容信息*/
        $this->assign($data);
        
        return $this->fetch();
	}

	public function xunhaun(Request $request){
        $route_name=request()->param()['brand'];
        if(isset(request()->param()['tag'])){
            $tag=request()->param()['tag'];
        }
		$brandid=1337;/*品牌页面的分类*/
		$data['base_data']=$this->base_data;
        $data['crumbs']=get_crumbs($brandid);/*面包屑*/ 
       
        $lily_brand=Db::table('lily_brand')->where([['status','=',1],['route_name','=',$route_name]])->find();
        if(empty($lily_brand)){
            header("Location:/product/brand"); 
            exit;
        }
        $data['crumbs'][]['alias_name']=$lily_brand['name'];
        
        $data['seo_title']=$lily_brand['title']?$lily_brand['title']:$lily_brand['name']." | Lily Bearing";
        $data['seo_keywords']=$lily_brand['keywords']?$lily_brand['keywords']:$lily_brand['name']." | Lily Bearing";
        $data['seo_desc']=$lily_brand['description']?$lily_brand['description']:$lily_brand['name']." | Lily Bearing";
        $get=request()->get();
        if(!empty($get)){
            $get=$this->move_ad_and_replace_p($get);
        }

        $products_class=Db::table('lily_products')->field('class_id')->where([['bid','=',$lily_brand['id']]])->group('class_id')->select();
		$class_data=array();
        $class_id_str='';
		$class_id_arr=[];
		foreach ($products_class as $key => $value) {
			$class_uplower_delete_first=class_uplower_delete_first($value['class_id']);
			$max=$class_uplower_delete_first[count($class_uplower_delete_first)-1]['id'];
			$min=$class_uplower_delete_first[0]['id'];
			$class_data[$max][]=$min;
            $class_id_str.=$value['class_id'].",";
			$class_id_arr[]=$value['class_id'];
		}
		$class_id_str=rtrim($class_id_str,',');


        $data['listkey']=Db::table('lily_brand_listkey')->where([['bid','=',$lily_brand['id']]])->order('seq')->select();
        if(empty($data['listkey'])){
        	if(empty($get)){
                $data['get']=[];
        		$brand=[];
				foreach ($class_data as $key => $value) {
					$brand[$key]['fu']=Db::table('lily_classify')->field('cname,alias_name')->where([['id','=',$key],['status','=',1]])->find();
					$brand[$key]['fu']['url']=get_class_uplower_url($key);
					foreach ($value as $k => $v) {
						$brand[$key]['zi'][$k]=Db::table('lily_classify')->field('cname,alias_name,img')->where([['id','=',$v],['status','=',1]])->find();
						$classify_content=Db::table('lily_classify_content')->where('class_id','=',$v)->find();/*分类的内容信息*/
						$brand[$key]['zi'][$k]['url']=get_class_uplower_url($v);
						$brand[$key]['zi'][$k]['classify_content']=$classify_content;
					}
				}
				$data['brand']=$brand;
			    $data['lily_brand']=$lily_brand;
        	}
        	$this->assign($data);
		    return $this->fetch();
        }else{
            $get_transition=$get;
            unset($get_transition['page']);
            $data['get']=$get_transition;

            $table_count=0;
            $products_class_new=[];
                
            foreach ($class_id_arr as $key => $value) {  
                $num_dan=$this->shu_heng_connt($value,$get_transition);

                if($num_dan!=0){
                    $table_count+=$num_dan;
                    $products_class_new[]['class_id']=$value;
                }
            }
            $class_data1=[];
            foreach ($products_class_new as $key => $value) {
                $class_uplower_delete_first1=class_uplower_delete_first($value['class_id']);
                $max1=$class_uplower_delete_first1[count($class_uplower_delete_first1)-1]['id'];
                $min1=$class_uplower_delete_first1[0]['id'];
                $class_data1[$max1][]=$min1;
            }

        	$data['table_count']=$table_count;
        	$brand=[];
            $fitle_id=[];
			foreach ($class_data1 as $key => $value) {
				$brand[$key]['fu']=Db::table('lily_classify')->field('cname,alias_name')->where([['id','=',$key],['status','=',1]])->find();
				$brand[$key]['fu']['url']=get_class_uplower_url($key);
				foreach ($value as $k => $v) {
                    $fitle_id[]=$v;
					$brand[$key]['zi'][$k]=Db::table('lily_classify')->field('cname,alias_name,img')->where([['id','=',$v],['status','=',1]])->find();
					$classify_content=Db::table('lily_classify_content')->where('class_id','=',$v)->find();/*分类的内容信息*/
                    $move_ad_parameter_url=explode('?',$this->base_data['move_ad_parameter_url']);
					$brand[$key]['zi'][$k]['url']=get_class_uplower_url($v).(isset($data['get'])&&$data['get']?'?'.$move_ad_parameter_url[1]:'');
					$brand[$key]['zi'][$k]['classify_content']=$classify_content;
				}
			}
			$data['brand']=$brand;
			$data['lily_brand']=$lily_brand;
            $Esproduct = controller('index/Esproduct');
            

            $jianc=0;
            foreach ($data['listkey'] as $key => $value) {
                if(empty($get_transition)){
                    $letget=array();
                }else{
                    $letget=array();
                    foreach ($get_transition as $k => $v) {
                        $letget[str_replace('_',' ',$k)]=$v;
                    }
                }
                $data['listkey'][$key]['data']=$Esproduct->search_data_api($letget,$class_id_arr,$value['wname'],$value['is_in_m']);
                $jianc=$jianc+count($data['listkey'][$key]['data']);
            }

            $data['current_url']=$this->base_data['current_url'];

            if($table_count<=Config::get('app.list_page_num')){
                $page_start=0;
                $class_id_str='';
                $table_products=array();

                //$right_table1=Db::table('lily_right_table1')->field('fileds,fileds_alias,seq,is_show')->where('class_id','=',$lily_brand['id'])->order('seq')->select();
                /*当数量小于指定数量时以多表头列表形式显示*/
                //dump($fitle_id);exit;
                foreach ($fitle_id as $key => $value) {
                    $fu = class_uplower_delete_first($value);
                    $fu = array_pop($fu);
                    $right_table1=Db::table('lily_right_table')->field('fileds,fileds_alias,seq,is_show')->where('class_id','=',$fu['id'])->order('seq')->select();
                    $right_table2=Db::table('lily_right_table')->field('fileds,fileds_alias,seq,is_show')->where('class_id','=',$value)->order('seq')->select();
                    if(!empty($right_table1)){
                        $right_table=[];
                        foreach ($right_table1 as $k1 => $v1) {
                            $findfield=Db::table('lily_products_detail')->where([['key','=',$v1['fileds']],['class_id','=',$value]])->find();
                            if(!empty($findfield)){
                                $right_table[]=$v1;
                            }
                        }

                    }else{
                        $right_table=$right_table2;
                    }
                    //$right_table=Db::table('lily_right_table')->field('fileds,fileds_alias,seq,is_show')->where('class_id','=',$value)->order('seq')->select();

                    $right_table_orders=Db::table('lily_right_table_order')->field('fileds,seq')->where('class_id','=',$value)->order('seq')->select();


                    $table_data=$this->shu_heng($right_table,$value,$right_table_orders,$page_start,$get_transition);

                    $table_products[$key]['class']=Db::table('lily_classify')->where([['status','=',1],['id','=',$value]])->find();

                    $table_products[$key]['class']['url']=get_class_uplower_url($value);
                    $table_products[$key]['classify_content']=Db::table('lily_classify_content')->where('class_id','=',$value)->find();/*分类的内容信息*/
                    foreach ($right_table as $k => $v) {
                        if($v['is_show']) {
                           
                            $table_products[$key]['table_title'][] = lang($v['fileds']);
                            $table_products[$key]['table_limit'][] = $v['fileds'];
                        }
                    }
                    $is_price=Db::table('lily_products')->where([['class_id','=',$value],['price','<>',0]])->find();
                    if(!empty($is_price)){
                        $table_products[$key]['is_show_price']=1;
                    }else{
                        $table_products[$key]['is_show_price']=0;
                    }

                    $table_products[$key]['detail']=$table_data;

                    $class_id_str.=$value.",";
                }

                $class_id_str=rtrim($class_id_str,',');

                $bid=Db::table('lily_products')->field('bid')->where('class_id','in',$class_id_str)->group('bid')->select();/*品牌id*/
                if(!empty($bid)){
                    $brand_data=array();
                    foreach ($bid as $key => $value) {
                        $brand_data[]=Db::table('lily_brand')->field('route_name,name,img')->where([['id','=',$value['bid']],['status','=',1]])->find();/*品牌id*/
                    }
                }else{
                    $brand_data=array();
                }

                $data['brand_data']=$brand_data;
                $data['table_products']=$table_products;
                $data['table_count']=$table_count;
                $this->assign($data);

                return $this->fetch('more_title_list');
            }else{
                $this->assign($data);
                return $this->fetch('have_tag_have_listkey');
            }

        }
	}


    /*获取筛选后的总数*/
   public function shu_heng_connt($class_id,$get_transition){

       $right_fields=[];
       if(!empty($get_transition)){
        foreach ($get_transition as $key => $value) {
            if($key!='part_number'){
                if(!in_array(str_replace('_',' ',$key),$right_fields)){
                   $right_fields[]=str_replace('_',' ',$key);
                }
            }
        }

          $sql="select count(pid) as num from (select a.*,b.part_number from ( SELECT pid,";
         
          foreach ($right_fields as $key => $value) {
             $sql.="max(case when `key` = '".$value."' then `value` else null end) as `".str_replace(' ','_',$value)."`,";
          }
          $sql=rtrim($sql,',');
          
          /*筛选*/
          if(empty($get_transition)){
             $where='';
          }else{
             $where=' where ';
             foreach ($get_transition as $key => $value) {
                     if($key=='part_number'){
                        $where.="b.`".$key."` like '%".$value."%' and ";
                    }else{
                        if(strpos($value,'~')!==false){
                            $range=explode('~',str_replace('mm','',str_replace('"','',$value)));
                            if($range[1]=='greater'){
                                $where.="a.`".$key."`>".$range[0]." and ";
                            }else{
                                $where.="a.`".$key."`>".$range[0]." and a.`".$key."`<=".$range[1]." and ";
                            }
                        }else{
                            $where.="a.`".$key."`='".$value."' and ";
                        }
                    }  
                }
             $where=rtrim($where,'and ');
          }

          $sql=rtrim($sql,",")." from lily_products_detail WHERE class_id = ".$class_id." group by pid ) as a left join lily_products as b on a.pid=b.id ".$where.") as c" ;
      }else{
        $sql="select  count(id) as num from lily_products where class_id=".$class_id;
      }
      
      return Db::Query($sql)[0]['num'];
   }

/*
 移除get参数中的广告参数
 并且get中 p 标签为 part_number，p是再url中显示p是为了美化。 p 目的是搜索型号
 */
    public function move_ad_and_replace_p($get){
        if(isset($get['p'])){
            $get['part_number']=$get['p'];
            unset($get['p']);
        }
        $lily_ad=Db::table('lily_ad')->field('pname')->select();
        foreach ($lily_ad as $key => $value) {
            unset($get[$value['pname']]);
        }
        return $get;
    }

    /*
竖表转横表 方便列表页 表格展示 排序等等问题
传入需要查询的key
将key作为字段
拼接sql 获取右侧数据
$right_fields 右侧需要的字段
$order_fields 排序需要的字段
$page_start 分页开始字段
*/
    public function shu_heng($right_fields,$class_id,$order_fields,$page_start,$get_transition){

        $System=Db::table('lily_products_detail')->where([['class_id','in',$class_id],['key','=','System of Measurement']])->find();
        if(!empty($System)&&!in_array('System of Measurement',$right_fields)){
            $add_right_fields['fileds']='System of Measurement';
            $add_right_fields['fileds_alias']=null;
            $add_right_fields['seq']=count($right_fields);
            $add_right_fields['is_show']=0;
            $right_fields[]=$add_right_fields;
        }
        $right_table_fileds=[];
        foreach ($right_fields as $key => $value) {
            $right_table_fileds[]=$value['fileds'];
        }
        $right_table_orders_arr=[];
        if(!empty($order_fields)){
            foreach ($order_fields as $key => $value) {
                $right_table_orders_arr[]=$value['fileds'];
            }
        }

        if(!empty($get_transition)){
            foreach ($get_transition as $key => $value) {
                if($key!='part_number'){
                    if(!in_array(str_replace('_',' ',$key),$right_table_fileds)){
                        $right_table_fileds[]=str_replace('_',' ',$key);
                    }
                }
            }
        }


        $sql="select b.part_number,a.*,b.price,b.url_product from ( SELECT pid,";


        foreach ($right_table_fileds as $key => $value) {
            $sql.="group_concat(case when `key` = '".$value."' then `value` else null end) as `".str_replace(' ','_',$value)."`,";
        }
        $sql=rtrim($sql,',');
        /*排序*/
        if(empty($right_table_orders_arr)){
            $order="";
        }else{
            $order="order by ";
            if(in_array('System of Measurement',$right_table_fileds)){
                $order.="a.`System_of_Measurement` ASC,";/*中文站变为desc*/
            }
            foreach ($right_table_orders_arr as $key => $value) {
                //$order.="a.`".str_replace(' ','_',$value)."`+0 asc,";
                $order.="( case when LOCATE(' ',a.`".str_replace(' ','_',$value)."`)>0 and LOCATE('\"',a.`".str_replace(' ','_',$value)."`)>0 then SUBSTRING_INDEX(REPLACE(a.`".str_replace(' ','_',$value)."`,'\"',''),' ',1)+(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(a.`".str_replace(' ','_',$value)."`,'\"',''),' ',-1),'/',1)/SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(a.`".str_replace(' ','_',$value)."`,'\"',''),' ',-1),'/',-1))  when LOCATE(' ',a.`".str_replace(' ','_',$value)."`)=0 AND LOCATE('/',a.`".str_replace(' ','_',$value)."`)>0  then  SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(a.`".str_replace(' ','_',$value)."`,'\"',''),' ',-1),'/',1)/SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(a.`".str_replace(' ','_',$value)."`,'\"',''),' ',-1),'/',-1) when LOCATE(' ',a.`".str_replace(' ','_',$value)."`)=0 AND LOCATE('/',a.`".str_replace(' ','_',$value)."`)=0 AND LOCATE(' mm',a.`".str_replace(' ','_',$value)."`)=0 AND LOCATE('\"',a.`".str_replace(' ','_',$value)."`)>0  then REPLACE(a.`".str_replace(' ','_',$value)."`,'\"','')+0 when LOCATE(' mm',a.`".str_replace(' ','_',$value)."`)>0 then REPLACE(a.`".str_replace(' ','_',$value)."`,' mm','')+0 END ) asc,";
                $order.= " case when a.`".str_replace(' ','_',$value)."` REGEXP '[0-9]'=1 then a.`".str_replace(' ','_',$value)."` end asc,";
            }
            $order=$order."replace(b.part_number,'S','') asc";
        }
        /*筛选*/
        if(empty($get_transition)){
            $where='';
        }else{
            $where=' where ';
            foreach ($get_transition as $key => $value) {
                if($key=='part_number'){
                    $where.="b.`".$key."` like '%".$value."%' and ";
                }else{
                    if(strpos($value,'~')!==false){
                        $range=explode('~',str_replace('mm','',str_replace('"','',$value)));
                        if($range[1]=='greater'){
                            $where.="a.`".$key."`>".$range[0]." and ";
                        }else{
                            $where.="a.`".$key."`>".$range[0]." and a.`".$key."`<=".$range[1]." and ";
                        }
                    }else{
                        $where.="a.`".$key."`='".$value."' and ";
                    }
                }
            }
            $where=rtrim($where,'and ');
        }

        $sql=rtrim($sql,",")." from lily_products_detail WHERE class_id = ".$class_id." group by pid ) as a left join lily_products as b on a.pid=b.id ".$where." ".$order." limit ".$page_start.",".Config::get('app.list_page_num');

        //echo $sql;exit;
        return Db::Query($sql);
    }

    /*分类向上取去掉最顶层一类*/
    function class_uplower_delete_first($id,$data=array()){
        $self=Db::table('lily_classify')->field('cname,route_name,fid,id,is_tag,route_id')->where([['status','=',1],['id','=',$id]])->order('seq','asc')->find();
        $data[]=$self;
        if($self['fid']==1){
            return $data;
        }else{
            return class_uplower_delete_first($self['fid'],$data);
        }
    }

    
}